package com.dashu.base.utils.excel;

import com.dashu.base.bean.excel.ExcelStudent;
import com.dashu.base.bean.excel.ExcelTeacher;
import com.dashu.base.enums.common.GenderType;
import com.dashu.base.enums.common.PersonalType;
import com.dashu.base.enums.school.excel.*;
import com.dashu.base.utils.File.FileUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


@Slf4j
public class ExcelUtil {


    /**
     * 导出Excel
     *
     * @param wb        工作簿HSSFWorkbook对象
     * @param excelName 工作表名称
     * @param excelName 工作表后缀名
     * @param response  http响应
     */
    public static void exportExcel(HSSFWorkbook wb, String excelName, String suffix, HttpServletResponse response) {

        try {
            // 设置response头信息
            response.reset();   // 清除buffer缓存
            response.setContentType("application/vnd.ms-excel;charset=utf-8"); // 改成输出excel文件
            //通知浏览器不要缓存
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", -1);
            //通知客户端以下载的方式打开
            response.setHeader("Content-Disposition", "attachment; filename="
                    + new String(excelName.getBytes("utf-8"), "ISO-8859-1") + suffix);
            //将excel文件输出到response输出流
            OutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            e.getMessage();
            System.out.println("导出Excel失败！");
            System.out.println(e.getMessage());
        }
    }


    public static HttpServletResponse exportData(HttpServletResponse response, ExcelName excelName, ExcelType excelType, List<Map<String, String>> excelMaps) {
        String excelName1 = excelName.name;
        HSSFWorkbook wb = new HSSFWorkbook();
//        wb.setSheetOrder("黑体",18);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 11);//设置字体大小
        cellStyle.setFont(font);
        //创建表

        if (ExcelType.TEACHER.equals(excelType)) {
            if (ExcelName.ATTENDANCEK_DAY.equals(excelName)) {
                exportTeacherDay(wb, cellStyle, excelMaps);
            } else {
                exportTeacherMonth(wb, cellStyle, excelMaps);
            }
        } else {
            if (ExcelName.ATTENDANCEK_DAY.equals(excelName)) {
                exportStudentDay(wb, cellStyle, excelMaps);
            } else {
                exportStudentMonth(wb, cellStyle, excelMaps);
            }
        }


        /*********最后使用工具类导出设置好的表格************/
        exportExcel(wb, excelName1, ".xls", response);
        return response;
    }


    /**
     * 导出Excel
     *
     * @param wb           工作簿HSSFWorkbook对象
     * @param cellStyle    工作表样式
     * @param excelDyaMaps 导表数据
     */

    public static void exportTeacherDay(HSSFWorkbook wb, HSSFCellStyle cellStyle, List<Map<String, String>> excelDyaMaps) {
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 800);
        ExcelTeacherDay[] excelTeacherDays = ExcelTeacherDay.values();
        //写入数据
        for (int rowNum = 0; rowNum <= excelDyaMaps.size(); rowNum++) {
            Row row = sheet.createRow(rowNum);
            if (rowNum == 0) {
                int cellNum = 0;
                for (ExcelTeacherDay excelTeacherDay : excelTeacherDays) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellValue(excelTeacherDay.cellName);
                    //渲染单元格
                    cell.setCellStyle(cellStyle);
                    cellNum++;
                }
            } else {
                Map<String, String> excelDyaMap = excelDyaMaps.get(rowNum - 1);
                int cellNum = 0;
                for (ExcelTeacherDay excelTeacherDay : excelTeacherDays) {
                    Cell cell = row.createCell(cellNum);
                    if (ExcelTeacherDay.ExcelIcons().contains(excelTeacherDay)) {
                        if (excelDyaMap.get(excelTeacherDay.key) != null && !excelDyaMap.get(excelTeacherDay.key).equals("")) {
                            picture(wb, sheet, row, excelDyaMap.get(excelTeacherDay.key), rowNum, cellNum);
                        } else {
                            cell.setCellValue("暂无");
                        }
                        cellNum++;
                    } else {
                        if (excelDyaMap.get(excelTeacherDay.key) != null && !excelDyaMap.get(excelTeacherDay.key).equals("")) {
                            cell.setCellValue(excelDyaMap.get(excelTeacherDay.key));
                        } else {
                            cell.setCellValue("暂无");
                        }

                        cellNum++;
                    }
                }
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param wb             工作簿HSSFWorkbook对象
     * @param cellStyle      工作表样式
     * @param excelMonthMaps 导表数据
     */

    public static void exportTeacherMonth(HSSFWorkbook wb, HSSFCellStyle cellStyle, List<Map<String, String>> excelMonthMaps) {
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 800);
        ExcelTeacherMonth[] excelTeacherMonths = ExcelTeacherMonth.values();
        //写入数据
        for (int rowNum = 0; rowNum <= excelMonthMaps.size(); rowNum++) {
            Row row = sheet.createRow(rowNum);
            if (rowNum == 0) {
                int cellNum = 0;
                for (ExcelTeacherMonth excelTeacherMonth : excelTeacherMonths) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellValue(excelTeacherMonth.cellName);
                    //渲染单元格
                    cell.setCellStyle(cellStyle);
                    cellNum++;
                }
            } else {
                Map<String, String> excelMonthMap = excelMonthMaps.get(rowNum - 1);
                int cellNum = 0;
                for (ExcelTeacherMonth excelTeacherMonth : excelTeacherMonths) {
                    Cell cell = row.createCell(cellNum);
                    if (ExcelTeacherMonth.ExcelIcons().contains(excelTeacherMonth)) {
                        if (excelMonthMap.get(excelTeacherMonth.key) != null && !excelMonthMap.get(excelTeacherMonth.key).equals("")) {
                            picture(wb, sheet, row, excelMonthMap.get(excelTeacherMonth.key), rowNum, cellNum);
                        } else {
                            cell.setCellValue("暂无");
                        }
                        cellNum++;
                    } else {
                        if (excelMonthMap.get(excelTeacherMonth.key) != null && !excelMonthMap.get(excelTeacherMonth.key).equals("")) {
                            cell.setCellValue(excelMonthMap.get(excelTeacherMonth.key));
                        } else {
                            cell.setCellValue("暂无");
                        }
                        cellNum++;
                    }
                }
            }
        }
    }


    /**
     * 导出Excel
     *
     * @param wb           工作簿HSSFWorkbook对象
     * @param cellStyle    工作表样式
     * @param excelDyaMaps 导表数据
     */

    public static void exportStudentDay(HSSFWorkbook wb, HSSFCellStyle cellStyle, List<Map<String, String>> excelDyaMaps) {
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 800);
        ExcelStudentDay[] excelStudentDays = ExcelStudentDay.values();
        //写入数据
        for (int rowNum = 0; rowNum <= excelDyaMaps.size(); rowNum++) {
            Row row = sheet.createRow(rowNum);
            if (rowNum == 0) {
                int cellNum = 0;
                for (ExcelStudentDay excelStudentDay : excelStudentDays) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellValue(excelStudentDay.cellName);
                    //渲染单元格
                    cell.setCellStyle(cellStyle);
                    cellNum++;
                }
            } else {
                Map<String, String> excelDyaMap = excelDyaMaps.get(rowNum - 1);
                int cellNum = 0;

                for (ExcelStudentDay excelStudentDay : excelStudentDays) {
                    Cell cell = row.createCell(cellNum);
                    if (ExcelStudentDay.ExcelIcons().contains(excelStudentDay)) {
                        if (excelDyaMap.get(excelStudentDay.key) != null && !excelDyaMap.get(excelStudentDay.key).equals("")) {
                            picture(wb, sheet, row, excelDyaMap.get(excelStudentDay.key), rowNum, cellNum);
                        } else {
                            cell.setCellValue("暂无");
                        }
                        cellNum++;
                    } else {
                        cell.setCellValue(excelDyaMap.get(excelStudentDay.key));
                        cellNum++;
                    }
                }
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param wb             工作簿HSSFWorkbook对象
     * @param cellStyle      工作表样式
     * @param excelMonthMaps 导表数据
     */

    public static void exportStudentMonth(HSSFWorkbook wb, HSSFCellStyle cellStyle, List<Map<String, String>> excelMonthMaps) {
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeight((short) 800);
        ExcelStudentMonth[] excelStudentMonthies = ExcelStudentMonth.values();
        //写入数据
        for (int rowNum = 0; rowNum <= excelMonthMaps.size(); rowNum++) {
            Row row = sheet.createRow(rowNum);
            if (rowNum == 0) {
                int cellNum = 0;
                for (ExcelStudentMonth excelStudentMonthy : excelStudentMonthies) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellValue(excelStudentMonthy.cellName);
                    //渲染单元格
                    cell.setCellStyle(cellStyle);
                    cellNum++;
                }
            } else {
                Map<String, String> excelMonthMap = excelMonthMaps.get(rowNum - 1);
                int cellNum = 0;
                for (ExcelStudentMonth excelStudentMonth : excelStudentMonthies) {
                    Cell cell = row.createCell(cellNum);
                    if (ExcelStudentMonth.ExcelIcons().contains(excelStudentMonth)) {
                        if (excelMonthMap.get(excelStudentMonth.key) != null && !excelMonthMap.get(excelStudentMonth.key).equals("")) {
                            picture(wb, sheet, row, excelMonthMap.get(excelStudentMonth.key), rowNum, cellNum);
                        } else {
                            cell.setCellValue("暂无");
                        }
                        cellNum++;
                    } else {
                        cell.setCellValue(excelMonthMap.get(excelStudentMonth.key));
                        cellNum++;
                    }
                }
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param wb    工作簿HSSFWorkbook对象
     * @param sheet 工作表
     * @param row   行
     */
    public static void picture(HSSFWorkbook wb, Sheet sheet, Row row, String path, int rows, int cols) {
        /************上面是设置表格文本内容，下面进行插入图片处理的部分***************/
        BufferedImage bufferImg = null;
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        //加载图片
        try {
            //这里file路径换成需要插入的图片路径
            bufferImg = ImageIO.read(FileUtils.downloadWxImage(path));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
//            这里获取上面插入数据后表格的最后一行
//            int lastRowNum = sheet1.getLastRowNum();
            // 设置B列的宽度为35*256；
//            sheet1.setColumnWidth(1, 35 * 256);
//            int width =20;//原始宽度
//            int height = 1;//原始高度
//            // 一个12号字体的宽度为13,前面已设置了列的宽度为30*256，故这里的等比例高度计算如下
//            height = (int) Math.round((height * (20 * 20) * 1 / width));
            // excel单元格高度是以点单位，1点=2像素; POI中Height的单位是1/20个点，故设置单元的等比例高度如下
            row.setHeight((short) (20 * 100));
            // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            // anchor主要用于设置图片的属性
//            System.out.println( cols+ "+:" + rows);
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cols, rows, (short) (cols + 1), rows + 1);
            anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
            // 插入图片
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出Excel
     *
     * @param inputStream 工作簿HSSFWorkbook对象
     * @param fileType    工作表名称
     */
    public static Workbook getWorkbook(InputStream inputStream, ExcelFileType fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equals(ExcelFileType.XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equals(ExcelFileType.XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 读取Excel文件内容
     *
     * @param inputStream 要读取的Excel文件流
     * @param fileType    要读取的Excel文件格式
     * @return 读取结果列表，读取失败时返回null
     */
    public static List readExcel(InputStream inputStream, ExcelFileType fileType, ExcelType excelType) {
        Workbook workbook = null;
//        InputStream inputStream = inputStream;
        try {
            // 获取Excel工作簿
//            inputStream = request.getInputStream();
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中的数据

            if (ExcelType.STUDENT.equals(excelType))
                return parseExcelStudent(workbook);
            if (ExcelType.TEACHER.equals(excelType))
                return parseExcelTeacher(workbook);
        } catch (Exception e) {
            log.info("解析Excel失败, 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.info("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
        return null;
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    public static List<ExcelStudent> parseExcelStudent(Workbook workbook) {
        List<ExcelStudent> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                log.info("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }
                int cellCount = 0;
                ExcelStudent excelStudent = new ExcelStudent();
                for (ExcelStudentType resultData : ExcelStudentType.values()) {
                    if (cellCount == 0) {
                        String name = row.getCell(cellCount) == null ? row.createCell(cellCount).getStringCellValue() : row.getCell(cellCount).getStringCellValue();
                        excelStudent.setName(name);
                    } else if (cellCount == 1) {
                        String num = row.getCell(cellCount) == null ? row.createCell(cellCount).getStringCellValue() : row.getCell(cellCount).getStringCellValue();
                        excelStudent.setNum(num);
                    } else if (cellCount == 2) {
                        PersonalType personalType = row.getCell(cellCount) == null ? null : PersonalType.getPersonalType(row.getCell(cellCount).getStringCellValue());
                        excelStudent.setPersonalType(personalType);
                    } else if (cellCount == 3) {
                        GenderType genderType = row.getCell(cellCount) == null ? null : GenderType.getGenderType(row.getCell(cellCount).getStringCellValue());
                        excelStudent.setGender(genderType);
                        resultDataList.add(excelStudent);
                    }
                    if (null == resultData) {
                        log.info("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                        continue;
                    }
                    cellCount++;
                }
            }
        }
        return resultDataList;
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    public static List<ExcelTeacher> parseExcelTeacher(Workbook workbook) {
        List<ExcelTeacher> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                log.info("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);

                if (null == row) {
                    continue;
                }
                int cellCount = 0;
                ExcelTeacher excelTeacher = new ExcelTeacher();
                for (ExcelTeacherType resultData : ExcelTeacherType.values()) {
                    //设置单元格类型
                    row.getCell(cellCount).setCellType(CellType.STRING);
                    if (cellCount == 0) {
                        String name = row.getCell(cellCount) == null ? row.createCell(cellCount).getStringCellValue() : row.getCell(cellCount).getStringCellValue();
                        excelTeacher.setName(name);
                    } else if (cellCount == 1) {
                        String phone = row.getCell(cellCount) == null ? row.createCell(cellCount).getStringCellValue() : row.getCell(cellCount).getStringCellValue();
                        excelTeacher.setPhone(phone);
                    } else if (cellCount == 2) {
                        GenderType genderType = row.getCell(cellCount) == null ? null : GenderType.getGenderType(row.getCell(cellCount).getStringCellValue());
                        excelTeacher.setGender(genderType);
                        resultDataList.add(excelTeacher);
                    }
                    if (null == resultData) {
                        log.info("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                        continue;
                    }
                    cellCount++;
                }

            }
        }
        return resultDataList;
    }

}
